﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace wssServer
{
    class MySqlConnector
    {
        static private MySqlConnection Connection;

        static public void setConnection()
        {
            string Server   = Functions.lerSubKey("DataBaseConf", "server");
            string User     = Functions.lerSubKey("DataBaseConf", "user");
            string Password = Functions.lerSubKey("DataBaseConf", "password");
            string Database = Functions.lerSubKey("DataBaseConf", "database");

            try
            {
                string connStr = String.Format("server={0}; user id={1}; password={2}; database={3}",
                    Server, User, Password, Database);

                Connection = new MySqlConnection(connStr);
            }
            catch (MySqlException ex)
            {
                Functions.gravarErro("Error connecting to the server: " + ex.Message);
            }
        }

        static public void Connect()
        {
            try
            {
                if (Connection == null)
                {
                    setConnection();
                }
                Connection.Open();
            }
            catch (MySqlException ex)
            {
                Functions.gravarErro("Erro ao conectar com o Servidor: " + ex.Message);
            }
        }

        static public void Disconnect()
        {
            try
            {
                if (Connection != null)
                    Connection.Close();
            }
            catch (MySqlException ex)
            {
                Functions.gravarErro("Erro ao desconectar com o Servidor: " + ex.Message);
            }
        }

        static public ArrayList Listar(string TableName, string Criteria)
        {
            try
            {
                Connect();
                if (Criteria != null)
                {
                    Criteria = " WHERE " + Criteria;
                }

                MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + TableName + Criteria, Connection);
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);

                ArrayList Classlist = new ArrayList();
                while (dr.Read())
                {
                    String[] ClassItem = new String[dr.FieldCount];

                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        string campo = dr.GetName(i);
                        ClassItem[i] = Convert.ToString(dr[campo]);
                    }

                    Classlist.Add(ClassItem);
                }

                return Classlist;
            }
            catch (MySqlException ex)
            {
                Functions.gravarErro("Erro ao listar " + TableName + ": " + ex.Message);
                return null;
            }
            finally
            {
                Disconnect();
            }
        }

        static public ArrayList Pesquisar(string SQL)
        {
            try
            {
                Connect();
                
                MySqlCommand cmd = new MySqlCommand(SQL, Connection);
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);

                ArrayList Classlist = new ArrayList();
                while (dr.Read())
                {
                    String[] ClassItem = new String[dr.FieldCount];

                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        string campo = dr.GetName(i);
                        ClassItem[i] = Convert.ToString(dr[campo]);
                    }

                    Classlist.Add(ClassItem);
                }

                return Classlist;
            }
            catch (MySqlException ex)
            {
                Functions.gravarErro("Erro ao listar SQL: " + ex.Message);
                return null;
            }
            finally
            {
                Disconnect();
            }
        }

        static public bool Query(string SQL)
        {
            try
            {
                Connect();

                MySqlCommand cmd = new MySqlCommand(SQL, Connection);

                return Convert.ToBoolean(cmd.ExecuteNonQuery());
            }
            catch (MySqlException ex)
            {
                Functions.gravarErro("Erro ao executar SQL: " + ex.Message);
                return false;
            }
            finally
            {
                Disconnect();
            }
        }

        static public int Inserir(string SQL)
        {
            try
            {
                Connect();

                MySqlCommand cmd = new MySqlCommand(SQL, Connection);

                cmd.ExecuteNonQuery();

                return Convert.ToInt32(cmd.LastInsertedId);
            }
            catch (MySqlException ex)
            {
                Functions.gravarErro("Erro ao executar SQL: " + ex.Message);
                return -1;
            }
            finally
            {
                Disconnect();                
            }
        }

        
    }
}
